/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package consultorio;

import com.mysql.jdbc.ResultSet;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.util.List;
import javax.swing.table.DefaultTableModel;

/**
 *
 * @author Carlos
 */
public class DataBase {

    protected Connection connection = null;
    private MysqlDataSource dataSource = null;
    private PreparedStatement statement = null;
    private ResultSet resSet = null;
    private String host = "localhost";
    private String user = "carlos";
    private String pass = "cbitdevel";
    private String db = "consultorio";

    public DataBase() { }

    protected void connect() {
        try {
            this.statement = null;
            this.dataSource = new MysqlDataSource();
            this.dataSource.setUser(this.user);
            this.dataSource.setPassword(this.pass);
            this.dataSource.setDatabaseName(this.db);
            this.dataSource.setServerName(this.host);
        } catch (Exception ex) {
            System.err.println("Ocurrio un error con los parametros " + ex);
        }
    }

    protected Boolean getConnection() {
        try {
            this.connect();
            this.connection = this.dataSource.getConnection();
            return true;
        } catch (Exception ex) {
            System.err.println("Ocurrio un error con la conexion a la base de datos " + ex);
        }
        return false;
    }

    protected void close() {
        try {
            this.statement = null;
            this.dataSource = null;
            this.connection.close();
            this.resSet.close();
        } catch (Exception ex) {
            System.err.println("Ocurrio un error al cerrar la conexion " + ex);
        }
    }

    public DefaultTableModel getPatient(List colName, List colValue, String patient) {
        String sql = "SELECT * From user";

        try {
            this.getConnection();
            int cols = colName.size() + 1;

            if (patient.length() > 1) {
                sql += " WHERE vcUsername LIKE'%" + patient + "%'";
            }
            this.statement = this.connection.prepareStatement(sql);
            this.resSet = (ResultSet) this.statement.executeQuery();

            DefaultTableModel model = new DefaultTableModel();
            model.setColumnCount(0);

            for (Object name : colValue) {
                model.addColumn(name.toString());
            }

            while (this.resSet.next()) {
                Object[] fila = new Object[cols];
                int i = 0;

                for (Object name : colName) {
                    fila[i] = this.resSet.getString(name.toString());
                    i++;
                }
                model.addRow(fila);
            }

            
            this.close();
            return model;
        } catch (Exception ex) {
            System.err.println("Error de conexion " + ex);
        }
        return null;
    }
}
